﻿<%@ WebHandler Language="C#" Class="RbProductExecuteDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
/**
 * Description: 热表厂生产执行管控看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2025.5.19
 */

public class RbProductExecuteDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getStepProcessInfo":
                        r = getStepProcessInfo(jObject);//批次加工进度
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getShipRateData":
                        r = getShipRateData(jObject);//转工管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getShipRateDetails":
                        r = getShipRateDetails(jObject);//转工管理明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportShipRateData":
                        exportShipRateData(jObject);//转工管理明细导出
                        break;
                    case "getApsRateData":
                        r = getApsRateData(jObject);//排产管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getApsRateDetails":
                        r = getApsRateDetails(jObject);//排产管理明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportApsRateData":
                        exportApsRateData(jObject);//转工管理明细导出
                        break;
                    case "getFeedbackRateData":
                        r = getFeedbackRateData(jObject);//问题快反
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFeedbackRateDetails":
                        r = getFeedbackRateDetails(jObject);//问题快反明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportFeedbackRateData":
                        exportFeedbackRateData(jObject);//问题快反明细导出
                        break;
                    case "getPhyRateData":
                        r = getPhyRateData(jObject);//理化管理
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPhyRateDetails":
                        r = getPhyRateDetails(jObject);//理化管理明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportPhyRateData":
                        exportPhyRateData(jObject);//理化管理明细导出
                        break;
                    case "getMonthProcessData":
                        r = getMonthProcessData(jObject);//月度加工情况统计
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMonthProcessDetails":
                        r = getMonthProcessDetails(jObject);//月度加工情况统计明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportMonthProcessData":
                        exportMonthProcessData(jObject);//月度加工情况统计明细导出
                        break;
                    case "getStepInfo":
                        r = getStepInfo(jObject);//工步加工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCheckPointResultInfo":
                        r = getCheckPointResultInfo(jObject);//检测项
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //批次加工进度（页面中间表格部分）
    Result getStepProcessInfo(JObject obj)
    {
        Result r = new Result();
        string sql = @"
SELECT cci.containerid,cci.containername,cci.workflowstepname,cci.workflowstepid,
       (SELECT MAX(sif.stepseq) maxseq FROM stepinfo sif WHERE sif.workflowstepid = cci.workflowstepid) maxseq,
       cci.workflowname,cci.workflowrevision,cci.productname,cci.productrevision,cci.productdescription,cci.qty,cci.specno,cci.specname
FROM containercurrentinfo cci
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep ws ON ws.workflowstepid = cci.workflowstepid
WHERE cci.containerstatus = 1 AND cci.isinstore = 0";
        //批次加工进度 不按时间筛选
        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        //批次加工进度 分厂、工区、班组3级过滤
        if (selectOrg.Count == 3)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            string teamid = selectOrg[2].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND ws.workcenterid = '{1}' AND ws.teamid = '{2}'", factoryid, workcenterid, teamid);
        }
        else if (selectOrg.Count == 2)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND ws.workcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            string factoryid = selectOrg[0].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }
        //零件号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        sql += " ORDER BY cci.containername,cci.workflowstepname";

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dtContainer = dto.DBTable;

        if (dto.DBTable.Rows.Count == 0)
        {
            r.data = new DataTable();
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }

        string containeridList = "";
        foreach (DataRow row in dtContainer.Rows)
        {
            containeridList += string.Format("'{0}',", row["containerid"].ToString());
        }
        containeridList = string.Format("{0}", containeridList.TrimEnd(','));

        sql = @"
SELECT cci.containerid,cci.containername,cci.workflowstepname,cci.workflowstepid,sf.stepinfoid,
       (SELECT MAX(sif.stepseq) maxseq FROM stepinfo sif WHERE sif.workflowstepid = cci.workflowstepid) maxseq,
       sf.stepno,sf.notes,sf.description,sf.stepseq,
       csd.createdate dispatchdate,
       csw.createdate reportdate,
       cki.createdate checkdate,
       CASE WHEN csd.createdate IS NULL THEN '未派工'
            WHEN csd.createdate IS NOT NULL AND csw.createdate IS NULL THEN '已派工'
            WHEN csw.createdate IS NOT NULL AND cki.createdate IS NULL THEN '已报工'
            WHEN cki.createdate IS NOT NULL THEN '已检验'
       END status                                      
FROM containercurrentinfo cci
INNER JOIN stepinfo sf ON sf.workflowstepid = cci.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN containerstepdispatch2 csd ON csd.containerid = cci.containerid AND csd.stepinfoid = sf.stepinfoid
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid=csd.containerstepdispatchid AND csw.isuse=0
LEFT JOIN containercheckinfo2 cki ON cki.containerstepworkreportid=csw.containerstepworkreportid AND cki.isuse=0
WHERE cci.containerid IN ({0})
ORDER BY cci.containername,cci.workflowstepname,sf.stepseq";
        DataTable dtSpecs = dal.GetDataTable(string.Format(sql, containeridList));

        dtContainer.Columns.Add("steps", typeof(DataTable));
        foreach (DataRow dr in dtContainer.Rows)
        {
            string filter = string.Format("containerid = '{0}' AND workflowstepid = '{1}'", dr["containerid"], dr["workflowstepid"]);
            DataRow[] rows = dtSpecs.Select(filter);
            if (rows.Length > 0)
            {
                DataTable dtFilter = rows.CopyToDataTable();
                foreach (DataColumn col in dtFilter.Columns)
                {
                    col.ColumnName = col.ColumnName.ToLower();
                }
                dr["steps"] = dtFilter;
            }
            else
            {
                dr["steps"] = new DataTable();
            }
        }

        foreach (DataColumn col in dtContainer.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r = new Result();
        r.data = dtContainer;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //转工管理公用sql
    string getShipRateSql(JObject obj)
    {
        string sql = @"
SELECT f.factoryname,f2.factoryname tofactoryname,wc.workcentername,cci.familylistnames,cci.productname,cci.productdescription,cci.containername,cci.qty,ws.workflowstepname,
       csd.senddate,csd.needcompletedate,e.fullname,csd.toacceptdate,
       DECODE(csr.completedate,NULL,'未完成','已完成') status
FROM containershiprecord csd
LEFT JOIN containershiprecive csr ON csd.containershiprecordid = csr.containershiprecordid AND csr.isuse = 0
INNER JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN factory f2 ON f2.factoryid = csd.tofactoryid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN employee e ON e.employeeid = csd.toacceptor
LEFT JOIN workcenter wc ON wc.workcenterid = csd.toworkcenterid
WHERE csd.toacceptdate IS NOT NULL AND csd.isuse = 0 AND cci.containerstatus <> 2";

        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        //转工管理 只过滤到分厂/工区
        if (selectOrg.Count == 3 || selectOrg.Count == 2)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            sql += string.Format(" AND csd.tofactoryid = '{0}' AND csd.toworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            string factoryid = selectOrg[0].ToString();
            sql += string.Format(" AND csd.tofactoryid = '{0}'", factoryid);
        }

        //转工接收时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND csd.toacceptdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND csd.toacceptdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", obj["endOfSelectMonth"].ToString());
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }
        //零件号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        if (obj["seriesName"] != null && obj["seriesName"].ToString() != "")
        {
            sql += " AND csr.completedate IS NULL";
        }
        return sql;
    }

    //转工管理
    Result getShipRateData(JObject obj)
    {
        string sql = @"
SELECT COUNT(1) total,
       SUM(DECODE(t.status,'已完成',1,0)) finishcount,
       SUM(DECODE(t.status,'未完成',1,0)) unfinishcount,
       ROUND(SUM(DECODE(t.status,'已完成',1,0))/COUNT(1),4) finishrate
FROM ({0}) t";
        sql = string.Format(sql, getShipRateSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //转工管理明细
    Result getShipRateDetails(JObject obj)
    {
        string sql = getShipRateSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //转工管理明细导出
    void exportShipRateData(JObject obj)
    {
        string sql = getShipRateSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["factoryname"].Caption = "主制厂";
        dtResult.Columns["tofactoryname"].Caption = "承制厂";
        dtResult.Columns["familylistnames"].Caption = "型别";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "转出工序";
        dtResult.Columns["senddate"].Caption = "转出时间";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["fullname"].Caption = "接收人";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["workcentername"].Caption = "工区";

        ExportHelper.ExportExcel(dtResult, "转工已接收未完成数据");
    }

    //排产管理公用sql
    string getApsRateSql(JObject obj)
    {
        string sql = @"
SELECT DISTINCT cci.mfgordername,cci.containername,cci.qty,cci.workflowstepname currentspec,sif.stepno,sif.description,
       wfb.workflowname || ':' || wf.workflowrevision workflowinfo,cci2.togetherflag,
       aps.starttime,aps.endtime,w.workcentername,te.teamname,aps.status
FROM apsplandetail2 aps
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid
LEFT JOIN team te ON te.teamid = aps.teamid
LEFT JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN stepinfo sif ON sif.stepinfoid = aps.stepinfoid
WHERE aps.isuse = 0";

        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        //排产管理 分厂/工区/班组3级过滤
        if (selectOrg.Count == 3)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            string teamid = selectOrg[2].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND aps.workcenterid = '{1}' AND aps.teamid = '{2}'", factoryid, workcenterid, teamid);
        }
        else if (selectOrg.Count == 2)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND aps.workcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            string factoryid = selectOrg[0].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        //排产计划结束时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND aps.endtime >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND aps.endtime <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", obj["endOfSelectMonth"].ToString());
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }
        //零件号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        //未下发
        if (obj["seriesName"] != null && obj["seriesName"].ToString() != "")
        {
            sql += " AND aps.status <> 1";
        }
        return sql;
    }

    //排产管理
    Result getApsRateData(JObject obj)
    {
        string sql = @"
SELECT COUNT(1) total,
       SUM(t.status) finishcount, 
       COUNT(1) - SUM(t.status) unfinishcount,
       ROUND(SUM(t.status)/COUNT(1),4) finishrate
FROM (SELECT DISTINCT containername,status FROM ({0})) t";
        sql = string.Format(sql, getApsRateSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //排产管理明细数据
    Result getApsRateDetails(JObject obj)
    {
        string sql = getApsRateSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //排产管理明细导出
    void exportApsRateData(JObject obj)
    {
        string sql = getApsRateSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("status");
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["stepno"].Caption = "工步号";
        dtResult.Columns["description"].Caption = "工步名称";
        dtResult.Columns["workflowinfo"].Caption = "排产工艺信息";
        dtResult.Columns["togetherflag"].Caption = "主卡批次";
        dtResult.Columns["starttime"].Caption = "计划开始时间";
        dtResult.Columns["endtime"].Caption = "计划结束时间";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";

        ExportHelper.ExportExcel(dtResult, "未完成任务明细导出数据");
    }

    //问题快反公共sql
    string getFeedbackRateSql(JObject obj)
    {
        string sql = @"
SELECT fpt.problemtype,u.username,up.createdate,up.problem_feedback_title,
       d.departmentname,DECODE(up.problem_status,'已关闭','已关闭','进行中') status
FROM bo_eu_mes_problem_feedback up
LEFT JOIN bpm_orguser_table u ON u.userid = up.createuser
LEFT JOIN bpm_orgdepartment_table d ON d.departmentid = u.departmentid
LEFT JOIN bo_eu_mes_problem_type fpt ON fpt.id = up.feedback_type
LEFT JOIN employee e ON 'MES_' || e.employeename = up.createuser
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
WHERE 1=1 ";
        //问题快反 只按提出人所在分厂过滤
        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        string factoryid = selectOrg[0].ToString();
        sql += string.Format(" AND f.factoryid = '{0}'", factoryid);

        //问题提出时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND up.createdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND up.createdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", obj["endOfSelectMonth"].ToString());
        }

        if (obj["seriesName"] != null && obj["seriesName"].ToString() != "")
        {
            sql += " AND NVL(up.problem_status,'未关闭') <> '已关闭'";
        }
        return sql;
    }

    //问题快反
    Result getFeedbackRateData(JObject obj)
    {
        string sql = @"
SELECT COUNT(1) total,SUM(DECODE(t.status,'已关闭',1,0)) finishcount, ROUND(SUM(DECODE(t.status,'已关闭',1,0))/COUNT(1),4) finishrate
FROM ({0}) t";
        sql = string.Format(sql, getFeedbackRateSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //问题快反数据钻取
    Result getFeedbackRateDetails(JObject obj)
    {
        string sql = getFeedbackRateSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //问题快反钻取导出
    void exportFeedbackRateData(JObject obj)
    {
        string sql = getFeedbackRateSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["problemtype"].Caption = "问题类型";
        dtResult.Columns["username"].Caption = "提出人";
        dtResult.Columns["departmentname"].Caption = "提出单位";
        dtResult.Columns["createdate"].Caption = "提出时间";
        dtResult.Columns["problem_feedback_title"].Caption = "问题描述";
        dtResult.Columns["status"].Caption = "状态";

        ExportHelper.ExportExcel(dtResult, "未关闭快反问题导出数据");
    }

    //理化管理公共sql
    string getPhyRateSql(JObject obj)
    {
        string sql = @"
SELECT cci.mfgordername,cci.productname,cci.productdescription,cci.containername,e.fullname,t.entrustqty,t.createdate,
       DECODE(t.reporturl,NULL,'未完成','已完成') status
FROM EntrustMeasurement t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN employee e ON e.employeeid = t.entrustorid
WHERE 1=1 ";
        //理化管理 只筛选到分厂
        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        string factoryid = selectOrg[0].ToString();
        sql += string.Format(" AND t.factoryid = '{0}'", factoryid);

        //申请时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.createdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND t.createdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", obj["endOfSelectMonth"].ToString());
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }

        //零件号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        if (obj["seriesName"] != null && obj["seriesName"].ToString() != "")
        {
            sql += " AND t.reporturl IS NULL";
        }
        return sql;
    }

    //理化管理
    Result getPhyRateData(JObject obj)
    {
        string sql = @"
SELECT COUNT(1) total,SUM(DECODE(t.status,'已完成',1,0)) finishcount, ROUND(SUM(DECODE(t.status,'已完成',1,0))/COUNT(1),4) finishrate
FROM ({0}) t";
        sql = string.Format(sql, getPhyRateSql(obj));
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //理化管理明细
    Result getPhyRateDetails(JObject obj)
    {
        string sql = getPhyRateSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportPhyRateData(JObject obj)
    {
        string sql = getPhyRateSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["entrustqty"].Caption = "委托数量";
        dtResult.Columns["fullname"].Caption = "委托人";
        dtResult.Columns["createdate"].Caption = "申请时间";
        dtResult.Columns["status"].Caption = "状态";

        ExportHelper.ExportExcel(dtResult, "理化未完成导出数据");
    }

    //月度加工情况统计
    string getMonthProcessDataSql(JObject obj)
    {
        string sql = @"
SELECT DISTINCT f.factoryname tofactoryname,w.workcentername,t.teamname,f2.factoryname fromfactoryname,cci2.containername pcontainername,
       sub.workflowstepname pworkflowstepname,
       cci.containername rbcontainername,aps.workflowstepname sworkflowstepname,cci.qty,cci.productname,cci.productdescription,
       aps.starttime,aps.endtime,EXTRACT(DAY FROM aps.endtime) dayofmonth,TRUNC(aps.endtime) monthdate,
       csd.senddate,csd.toacceptdate,e.fullname acceptorname,
       csr.checkdate,
       CASE WHEN csr.checkdate IS NULL THEN '待完成'
            WHEN csr.checkdate IS NOT NULL THEN '已完成'
       END status
FROM apsplandetail2 aps --热表排产
INNER JOIN containercurrentinfo cci ON cci.containerid = aps.containerid --热表子卡
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN workcenter w ON w.workcenterid = aps.workcenterid
INNER JOIN team t ON t.teamid = aps.teamid
LEFT JOIN containersubcontainer sub ON sub.subcontainerid = aps.containerid
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.containerid --主卡
LEFT JOIN factory f2 ON f2.factoryid = cci2.originalfactoryid
INNER JOIN apsplandetail2 aps2 ON aps2.containerid = sub.containerid AND aps2.workflowstepid = sub.workflowstepid AND aps2.isuse = 0 AND NVL(aps2.status,0) != 0
INNER JOIN containershiprecordstep spd ON spd.containerid = cci2.containerid AND spd.workflowstepid = sub.workflowstepid --主卡转工工序
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = spd.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csrv ON csrv.containershiprecordid = csd.containershiprecordid AND csrv.isuse = 0
LEFT JOIN containersteprecord csr ON csr.containerid = aps.containerid AND csr.workflowstepid = aps.workflowstepid AND csr.usecompleted = 1 --热表子卡加工记录
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
LEFT JOIN employee e ON e.employeeid = csd.toacceptor
WHERE aps.isuse = 0 AND c.rbcontainertype IS NOT NULL AND csd.toacceptdate IS NOT NULL AND cci.containerstatus <> 2 AND ws.sequence = 1";

        JArray selectOrg = JArray.Parse(obj["selectOrg"].ToString());
        //月度加工情况统计 按分厂/工区/班组3级过滤
        if (selectOrg.Count == 3)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            string teamid = selectOrg[2].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND aps.workcenterid = '{1}' AND aps.teamid = '{2}'", factoryid, workcenterid, teamid);
        }
        else if (selectOrg.Count == 2)
        {
            string factoryid = selectOrg[0].ToString();
            string workcenterid = selectOrg[1].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND aps.workcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            string factoryid = selectOrg[0].ToString();
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        //排产计划结束时间
        if (obj["startOfSelectMonth"] != null && obj["startOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND aps.endtime >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", obj["startOfSelectMonth"].ToString());
        }
        if (obj["endOfSelectMonth"] != null && obj["endOfSelectMonth"].ToString() != "")
        {
            sql += string.Format(" AND aps.endtime <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", obj["endOfSelectMonth"].ToString());
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }
        //零件号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        if (obj["seriesName"] != null && obj["seriesName"].ToString() != "")
        {
            sql += " AND csr.checkdate IS NULL";
        }
        if (obj["endtime"] != null && obj["endtime"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(aps.endtime) = to_date('{0}','YYYY/MM/DD')", obj["endtime"].ToString());
        }
        return sql;
    }

    //月度加工情况统计
    Result getMonthProcessData(JObject obj)
    {
        //string sql = @"
        //SELECT t.monthdate,t.dayofmonth,COUNT(1) total,SUM(DECODE(t.status,'已完成',1,0)) finishcount,ROUND(SUM(DECODE(t.status,'已完成',1,0))/COUNT(1),4) * 100 finishrate
        //FROM ({0}) t
        //GROUP BY t.dayofmonth,t.monthdate
        //ORDER BY t.monthdate";
        string sql = @"
SELECT m.monthdate,m.dayofmonth,
       NVL(n.total,0) total,
       NVL(n.finishcount,0) finishcount,
       NVL(n.finishrate,100) finishrate
FROM (
  SELECT DATE '{1}' + LEVEL - 1 AS monthdate,EXTRACT(DAY FROM (DATE '{1}' + LEVEL - 1)) dayofmonth
  FROM dual
  CONNECT BY LEVEL <= (DATE '{2}' - DATE '{1}' + 1)
) m 
LEFT JOIN (
  SELECT t.monthdate,t.dayofmonth,COUNT(1) total,SUM(DECODE(t.status,'已完成',1,0)) finishcount,ROUND(SUM(DECODE(t.status,'已完成',1,0))/COUNT(1),4) * 100 finishrate
  FROM (
      {0}
  ) t
  GROUP BY t.dayofmonth,t.monthdate
) n ON n.monthdate = m.monthdate
ORDER BY m.dayofmonth,m.monthdate";
        sql = string.Format(sql, getMonthProcessDataSql(obj), obj["startOfSelectMonth"].ToString(), obj["endOfSelectMonth"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //月度加工情况统计明细
    Result getMonthProcessDetails(JObject obj)
    {
        string sql = getMonthProcessDataSql(obj);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //月度加工情况统计明细导出
    void exportMonthProcessData(JObject obj)
    {
        string sql = getMonthProcessDataSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns.Remove("dayofmonth");
        dtResult.Columns.Remove("monthdate");
        dtResult.Columns.Remove("checkdate");

        dtResult.Columns["fromfactoryname"].Caption = "主制分厂";
        dtResult.Columns["tofactoryname"].Caption = "承制分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["pcontainername"].Caption = "主卡流水卡号";
        dtResult.Columns["pworkflowstepname"].Caption = "主卡工序";
        dtResult.Columns["rbcontainername"].Caption = "热表批次号";
        dtResult.Columns["sworkflowstepname"].Caption = "热表工序";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["starttime"].Caption = "加工开始时间";
        dtResult.Columns["endtime"].Caption = "加工结束时间";
        dtResult.Columns["senddate"].Caption = "转工发出时间";
        dtResult.Columns["toacceptdate"].Caption = "转工接收时间";
        dtResult.Columns["acceptorname"].Caption = "转工接收人";
        dtResult.Columns["status"].Caption = "状态";

        ExportHelper.ExportExcel(dtResult, "月度加工情况统计未完工明细导出");
    }

    //工步加工情况
    Result getStepInfo(JObject obj)
    {
        string sql = @"
SELECT si.stepno,si.description stepinfodesc,csd.createdate dispatchdate,e.fullname dispatchfullname,
       csd.qty dispatchqty,
       listagg(e2.fullname,'、') WITHIN GROUP (ORDER BY e2.fullname) dispatchworkname,
       csd.containerstepdispatchid,csw.createdate reportdate,
       rd.description resourcedesc,e3.fullname reportemployeename,t.teamname,csw.qty reportqty,cci.createdate checkdate,
       e4.fullname checkemployeename,cci.acceptanceqty2 passqty,cci.lossqty  
FROM containerstepdispatch2 csd
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid=csd.containerstepdispatchid
LEFT JOIN employee e ON e.employeeid=csd.employeeid--派工人
LEFT JOIN employee e2 ON e2.employeeid=NVL(csd.workerid,csdd.workerid)--派给工人
LEFT JOIN stepinfo SI ON si.stepinfoid=csd.stepinfoid
LEFT JOIN resourcedef rd ON rd.resourceid=csd.resourceid
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid=csd.containerstepdispatchid AND csw.isuse=0
LEFT JOIN employee e3 ON e3.employeeid=csw.workerid
LEFT JOIN team t ON t.teamid=csw.teamid
LEFT JOIN containercheckinfo2 cci ON cci.containerstepworkreportid=csw.containerstepworkreportid AND cci.isuse=0
LEFT JOIN employee e4 ON e4.employeeid=cci.checkerid 
WHERE csd.isuse=0 AND csd.containerid='{0}' AND csd.workflowstepid='{1}' AND csd.stepinfoid = '{2}'
GROUP BY si.stepno,si.description ,csd.createdate ,e.fullname ,csd.qty,csd.containerstepdispatchid,csw.createdate,
         rd.description,e3.fullname,t.teamname,csw.qty,cci.createdate,e4.fullname,cci.acceptanceqty2,cci.lossqty,si.stepseq 
ORDER BY si.stepseq";
        sql = string.Format(sql, obj["containerid"].ToString(), obj["workflowstepid"].ToString(), obj["stepinfoid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //检测项
    Result getCheckPointResultInfo(JObject obj)
    {
        string sql = @"
SELECT scp.name,scp.checkpointnotedis_b,scp.drawingrequiredis_b,DECODE(scp.checkinputtype, '1', '标准', '2', '临时', checkinputtype) checkinputtypename, 
       DECODE(scp.checktype, '1', '专用', '2', '共用', checktype) checktypename,DECODE(scp.keycheck, '1', '是', '0', '否', keycheck) keycheckname, 
       DECODE(scp.meopcheck, '1', '是', '0', '否', meopcheck) meopcheckname,DECODE(scp.qualitycheck, '1', '是', '0', '否', qualitycheck) qualitycheckname, 
       DECODE(scp.tccheckpointnote, NULL, '否', '是') istcm,e2.fullname workername,
       scpr.createdate,scpr.workcheckpointresultnotedis_b, e1.fullname checkername,scpr.checkdate,
       scpr.checkpointresultnote,c.containername,scpr.checkpointresultnotedis_b,scpr.workcheckpointresultnotedis
FROM stepcheckpointresult scpr 
INNER JOIN stepcheckpoint scp ON scp.stepcheckpointid=scpr.stepcheckpointid  
LEFT JOIN employee e1 ON e1.employeeid=scpr.checkerid 
LEFT JOIN employee e2 ON e2.employeeid=scpr.workerid  
LEFT JOIN container c ON c.containerid = scpr.childcontainerid 
WHERE scpr.containerid='{0}' AND scp.workflowstepid='{1}' AND scp.stepinfoid = '{2}'";
        sql = string.Format(sql, obj["containerid"].ToString(), obj["workflowstepid"].ToString(), obj["stepinfoid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}
